package com.dal;
 

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
 
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Arrays;
 
/**
 * Poi - Excel 读取
 * 2020-12-23 16:51
 * @author win
 */


@Component
public class PoiExcelReader {

    private static final Logger log = LoggerFactory.getLogger(Main.class);

    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";
    /**
     * 默认表头 行数 (后续可调整增加入参动态调整行数)
     */
    private static final Integer DEFAULT_HEAD = 2;
 
    public static void main(String[] args) {
        String filePath ="/Users/jian/Downloads/财管已被使用供应商-2022-12-12(1).xlsx";
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(filePath);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        PoiExcelReader reader = new PoiExcelReader();
        reader.readExcel(inputStream);
    }
 
    /**
     * 获取工作簿
     * @author guoSt
     * @date 2020/12/23 16:53
     * @param inputStream:
     * @param fileType:
     * @return :
     */
    private Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
        Workbook workbook = null;
        if (fileType.equalsIgnoreCase(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileType.equalsIgnoreCase(XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }
 
 
    /**
     * 读取 Excel文件 内容
     * @author guoSt
     * @date 2020/12/23 17:03
     * @param inputStream:
     */
    private  void readExcel(InputStream inputStream) {
        Workbook workbook = null;
        try {
 
            workbook = getWorkbook(inputStream, XLSX);
 
            analyzeExcel(workbook);
 
        } catch (Exception e) {
            log.error(e.toString());
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                log.error("关闭数据流出错！错误信息：" + e.getMessage());
            }
        }
    }
    /**
     * 解析文件内容
     * @author guoSt
     * @date 2020/12/23 17:06
     * @param wb:
     */
    private  void analyzeExcel(Workbook wb){
        //读取第一个 sheet
        Sheet sheet= wb.getSheetAt(0);
        //获取行数
        int rowNum=sheet.getLastRowNum();
        if(rowNum < DEFAULT_HEAD){
            log.info("Excel没有内容需要读取！");
            return;
        }
        //i = 2 跳过表头 (模板表头占了两行)
        for(int i = 2; i <= rowNum ; i++) {
            //获取当前行
            Row row = sheet.getRow(i);
            //获取列数
            int colNum = row.getLastCellNum();
 
            for (int j = 0; j < colNum; j++) {
                //单元格
                Cell cell = row.getCell(j);
                CellStyle cellStyle = cell.getCellStyle();
                // xlsx 07版
                // 为了解决：NUMERIC cell
                cell.setCellType(CellType.STRING);
 
                //1、取内容  需要对列数进行特殊判断
                System.out.println(String.format("第：%s行，第%s列，单元格内容：%s", i, j, cell.getStringCellValue()));
 
                //2、批注信息
                if (cell.getCellComment() != null) {
                    System.out.println("批注：" + cell.getCellComment().getString().toString());
 
                    //3、取颜色信息
                    XSSFColor xssfColor = (XSSFColor) cellStyle.getFillForegroundColorColor();
                    if (null != xssfColor) {
                        //得到rgb的byte数组
                        byte[] bytes = xssfColor.getRGB();
                        //得到argb十六进制字符串
                        String argbHex = xssfColor.getARGBHex();
 
                        System.out.print("argbHex：" + argbHex);
                        System.out.print("颜色：" + Arrays.toString(bytes));
 
                        for (int k = 0; k < bytes.length; k++) {
                            System.out.print(bytes[k] + "-");
                        }
                    }
                }
            }
 
        }
    }
 
}